/**
 * 
 */
package valfazz.android.karfinder.database;

import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;

import valfazz.android.karfinder.pojo.Playlist;
import valfazz.android.karfinder.pojo.Song;
import valfazz.android.karfinder.pojo.SpinnerItem;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author Val
 *
 */
public class DatabaseHelper {
	private static final String DATABASE_NAME = "KarFinder.db";
	private static final int DATABASE_VERSION = 1;
	private static final String[] TABLE_NAMES = { "SONG", "PLAYLIST", "INCLUDE" };
	public ArrayList<Playlist> listPlaylist;
	public List<Song> searchList;
	private SQLiteDatabase db = null;
	
	public DatabaseHelper(Context context) {
		open(context);
		listPlaylist = new ArrayList<Playlist>();
		searchList = new ArrayList<Song>();
	}
	
	public void close() {
		if (db.isOpen())
			db.close();
	}
	
	public void open(Context context) {
		if (db == null || !db.isOpen())
			this.db = (new OpenHelper(context)).getWritableDatabase();
	}
	
	public SQLiteDatabase getDB() {
		return db;
	}
	
	public Song getSong(String songId) {
		Cursor cursor = db.rawQuery("select SONG_ID, SONG_TITLE, SONG_LYRICS, SONG_COMPOSER, " +
									"SONG_GENRE, SONG_IS_FAVORITE from SONG where SONG_ID = " +
									songId, null);
		Song song = null;
		
		if (cursor.moveToNext())
			song = new Song(cursor.getInt(0), cursor.getString(1),
							cursor.getString(2), cursor.getString(3),
							cursor.getString(4), cursor.getInt(5));
		else song = new Song(); 
		
		cursor.close();
		return song;
	}
	
	public void insertSong(JSONArray value) throws JSONException {
		if (!this.db.isOpen()) {
			Log.i("KarFinder DB", "Database is not open!!!");
			return;
		}
		
		ContentValues content = new ContentValues();
		content.put("SONG_ID", value.getInt(0));
		content.put("SONG_TITLE", value.getString(1));
		content.put("SONG_LYRICS", value.getString(2));
		content.put("SONG_COMPOSER", value.getString(3));
		content.put("SONG_LANGUAGE", value.getString(4));
		content.put("SONG_GENRE", value.getString(5));
		content.put("SONG_VOLUME", value.getInt(6));
		content.put("SONG_IS_FAVORITE", 0);
		content.put("TEN_BAI_HAT", value.getString(7));
		content.put("LOI_MO_DAU", value.getString(8));
		content.put("TAC_GIA", value.getString(9));
		db.insert("SONG", "TAC_GIA", content);
		// Log.i("INSERT preparedStatement", content.toString());
	}
	
	public long addPlaylist(Playlist playlist){
		ContentValues content = new ContentValues();
		content.put("PLAYLIST_NAME", playlist.getName());
		return db.insert("PLAYLIST", "PLAYLIST_NAME", content);
	}
	
	public long updatePlaylist(Playlist playlist){
		ContentValues content = new ContentValues();
		content.put("PLAYLIST_NAME", playlist.getName());
		return db.update("PLAYLIST", content, "PLAYLIST_ID = " + playlist.getId(), null);
	}
	
	public long deletePlaylist(Playlist playlist){
		db.delete("INCLUDE", "PLAYLIST_ID = " + playlist.getId(), null);
		return db.delete("PLAYLIST", "PLAYLIST_ID =" + playlist.getId(), null);
	}
	
	public long addSongToPlaylist(int playlistId, int songId) {
		Cursor cursor = db.rawQuery("select * from INCLUDE where " +
									"PLAYLIST_ID = " + playlistId + 
									" and SONG_ID = " + songId, null);
		long result = -1;
		if (cursor.getCount() == 0) {
			ContentValues values = new ContentValues();
			values.put("PLAYLIST_ID", playlistId);
			values.put("SONG_ID", songId);
			result = db.insert("INCLUDE", null, values);
		}
		cursor.close();
		return result;
	}
	
	public long deleteSongOfPlaylist(int playlistId, int songId){
		return db.delete("INCLUDE", "PLAYLIST_ID = " + playlistId + " and SONG_ID = " + songId, null);
	}
	
	public boolean isEmptyDatabase() {		
		Cursor cursor = db.rawQuery("SELECT SONG_ID FROM SONG LIMIT 1", null);
		boolean result = (cursor.getCount() > 0 ? false : true);
		cursor.close();
		return result;
	}
	
	public void updateSongList(String query) {
		Cursor cursor = db.rawQuery(query + " order by SONG_TITLE", null);
		int idIndex = cursor.getColumnIndex("SONG_ID");
		int titleIndex = cursor.getColumnIndex("SONG_TITLE");
		int lyricsIndex = cursor.getColumnIndex("SONG_LYRICS");
		searchList.clear();
		while (cursor.moveToNext()) {
			/*
			Song song = new Song();
			song.setId(cursor.getInt(idIndex));
			song.setTitle(cursor.getString(titleIndex));
			song.setLyrics(cursor.getString(lyricsIndex));*/
			searchList.add(
					new Song(cursor.getInt(idIndex),
							 cursor.getString(titleIndex),
							 cursor.getString(lyricsIndex)));
			//Log.i("song info", song.toString());
		}
		cursor.close();
	}	
	
	public ArrayList<Playlist> fetchPlaylistList() {
		Cursor cursor = db.rawQuery("select * from PLAYLIST order by PLAYLIST_NAME", null);
		int idIndex = cursor.getColumnIndex("PLAYLIST_ID");
		int nameIndex = cursor.getColumnIndex("PLAYLIST_NAME");
		
		listPlaylist.clear();
		
		while (cursor.moveToNext()) {
			Playlist playlist = new Playlist();			
			playlist.setId(cursor.getInt(idIndex));
			playlist.setName(cursor.getString(nameIndex));
			listPlaylist.add(playlist);
		}
		
		cursor.close();
		return listPlaylist;
	}	
	
	public ArrayList<SpinnerItem> fetchSpinnerList() {		
		Cursor cursor = db.rawQuery("select distinct SONG_VOLUME from SONG order by SONG_VOLUME desc limit 5", null);		
		ArrayList<SpinnerItem> list = new ArrayList<SpinnerItem>();
		while(cursor.moveToNext()) {
			String number= cursor.getString(0);
			list.add(new SpinnerItem("Volume " + number, number, "vol"));
			//Log.i("INSERT Spinner list","Volume "+ number);
		}
		cursor.close();
		list.add(new SpinnerItem("Tân Nhạc", "tan", "gen"));
		list.add(new SpinnerItem("Cổ Nhạc", "co", "gen"));
		list.add(new SpinnerItem("Thiếu Nhi", "nhi", "gen"));
		list.add(new SpinnerItem("Bài hát tiếng Việt", "vn", "lan"));
		list.add(new SpinnerItem("Bài hát tiếng Anh", "en", "lan"));
		return list;
	}
	
	public void setFavorite(String songId, boolean isAdded) {
		ContentValues values = new ContentValues();
		values.put("SONG_IS_FAVORITE", (isAdded == true ? 1 : 0));
		int rowAffected = db.update("SONG", values, "SONG_ID = " + songId, null);
		Log.i("UPDATE FAVORITE", rowAffected + " row(s)");
	}
	
	public int getLastestOfflineVolume() {
		// FOR TEST ONLY
		// TODO REMOVE THIS STATEMENT BEFORE RELEASE
		// testUpdater_delete_lastest_volume_43();
		
		Cursor cursor = db.rawQuery("select distinct SONG_VOLUME from SONG " +
									"order by SONG_VOLUME desc limit 1", null);
		int volume = -1;
		if (cursor.moveToNext()) {
			volume = cursor.getInt(0);
		}
		cursor.close();
		return volume;
	}

	public void testUpdater_delete_lastest_volume_43() {
		int rows = db.delete("SONG", "SONG_VOLUME > 44", null);
		Log.i("DELETE VOL43", rows + " rows affected");
	}
	
	private static class OpenHelper extends SQLiteOpenHelper {
		public OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {			
			String[] strQuery = {
					"create table PLAYLIST (" +
						"PLAYLIST_ID          integer primary key autoincrement not null," +
						"PLAYLIST_NAME        text" + ");",

					"create table SONG (" +
						"SONG_ID              int primary key not null," +
						"SONG_TITLE           text," +
						"SONG_LYRICS          text," +
						"SONG_COMPOSER        text," +
						"SONG_LANGUAGE        text," +
						"SONG_GENRE           text," +
						"SONG_VOLUME          int," +
						"SONG_IS_FAVORITE     int," + 
						"TEN_BAI_HAT          text," +
						"LOI_MO_DAU           text," +
						"TAC_GIA              text" + ");",

					"create table INCLUDE (" +
						"PLAYLIST_ID          integer not null," +
						"SONG_ID              int not null," +
						"primary key (PLAYLIST_ID, SONG_ID)," +
						"constraint FK_PLAYLIST_INCLUDE foreign key (PLAYLIST_ID) " +
						"references PLAYLIST (PLAYLIST_ID) on delete cascade on update cascade, " +
						"constraint FK_INCLUDE_SONG foreign key (SONG_ID) " +
						"references SONG (SONG_ID) on delete cascade on update cascade" + ");"
			};

			for (int i = 0; i < strQuery.length; ++i)
				db.execSQL(strQuery[i]);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db,  int oldVersion, int newVersion) {
			for (int i = 0; i < TABLE_NAMES.length; ++i)
				db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAMES[i]);
			onCreate(db);
		}
	}
}
